﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.IO;
using System.Data;
using System.Data.OleDb;
using LCUV;
using System.Web.Configuration;
using Oracle.DataAccess.Client;

namespace sWarranty.WEB
{
    public partial class ModelREGxls : System.Web.UI.Page
    {
        public string _currentUserEmpNo = "";
        public string _currentUserAuthority = "";
        protected void Page_Load(object sender, EventArgs e)
        {
            Session["UrltoFw"] = "SchemeMgr.aspx";
            _currentUserEmpNo = GetUserLoginEmpNo();
            if (_currentUserEmpNo == "")
                Response.Redirect("login.aspx");

            if (!IsPostBack)
            {
                Session["filePath_model_xls"] = "";
                Session["importTable_model_xls"] = "";//table after import data
                Session["fileName_model_xls"] = Guid.NewGuid().ToString();
            }

        }
        protected string GetUserLoginEmpNo()
        {
            string t = "";
            try
            {
                if (Session["UserLoginInfo"] != null)
                {
                    Entities db = new Entities();
                    var u = (TB_USER[])Session["UserLoginInfo"];
                    int _UID = Convert.ToInt32(u.Single().ID);
                    var au = from ft in db.TB_USER_LEVEL
                             where ft.USER_ID == _UID
                             select ft;
                    int _le = Convert.ToInt32(au.Single().LEVEL_ID);
                    var le = from xx in db.TB_LEVEL
                             where xx.ID == _le
                             select xx;
                    _currentUserAuthority = le.Single().REMARK;
                    return u.Single().EMPNO;
                }
            }
            catch (Exception)
            {
                t = "";
            }
            return t;
        }

        protected void AsyncFileUpload1_UploadedComplete(object sender, AjaxControlToolkit.AsyncFileUploadEventArgs e)
        {

            string fileName = Session["fileName_model_xls"].ToString();
            string _filePath = Request.PhysicalApplicationPath + fileName + ".xls";// +AsyncFileUpload1.PostedFile.FileName;
            if (File.Exists(_filePath))
            {
                File.Delete(_filePath);
            }
            else
            {
                AsyncFileUpload1.SaveAs(_filePath);
                //_filePath = EncDec.Encrypt(_filePath, "");
                Session["filePath_model_xls"] = _filePath;
                txtFileName.Text = fileName;
                //ScriptManager.RegisterClientScriptBlock(AsyncFileUpload1, AsyncFileUpload1.GetType(), Guid.NewGuid().ToString(), "ShowExcelData()", true);
                //readExcel(false);
            }
        }
        protected string readExcel(bool SaveToDB)
        {
            string schemeID = Request.QueryString["s"].ToString();
            string path = Session["filePath_model_xls"].ToString();
            string fileName = Session["fileName_model_xls"].ToString();
            string ReturnValue = "";
            txtRowToUnSave.Text = "";

            try
            {

                if (!path.EndsWith(".xls"))
                {
                    ReturnValue = "System just support Excel version 2003 (*.xls) only";
                    return ReturnValue;
                }
                if (string.IsNullOrEmpty(path))
                {
                    ReturnValue = "There are something wrong with file path. please reload page and import again";
                    return ReturnValue;
                }
                if (!File.Exists(path))
                {
                    ReturnValue = "File not found. Please select new file to upload";
                    return ReturnValue;
                }
                DataTable dtExcel = new DataTable();
                string err = "DONE";
                dtExcel.TableName = "MyExcelData";
                string SourceConstr = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source='" + path + "';Extended Properties= 'Excel 8.0;HDR=Yes;IMEX=1'";
                OleDbConnection con = new OleDbConnection(SourceConstr);
                string query = "Select * from [Sheet1$]";
                OleDbDataAdapter data = new OleDbDataAdapter(query, con);
                data.Fill(dtExcel);
                data.Dispose();
                string st = "";

                Entities db = new Entities();
                //TB_CM_SHOP_INF sn[] = new TB_CM_SHOP_INF();
                //ko tao dc kieu de ep vao mang, fai dung mang[] binh thuong de add feild ID
                ObjectThayThe[] sn = new ObjectThayThe[1000];
                int row = 1;
                DataTable dt = new DataTable();
                foreach (DataRow item in dtExcel.Rows)
                {
                    err = "DONE";
                    sn[row] = new ObjectThayThe();
                    sn[row].string1 = "row_" + row; //ID
                    sn[row].string2 = schemeID;
                    sn[row].string3 = item["MODEL"].ToString();
                    sn[row].string4 = item["POINT"].ToString();
                    sn[row].string5 = item["AMOUNT"].ToString();
                    query = "select pk_som_mst.FN_INCT_MD_UPL_VAL('" + sn[row].string2 + "','" + sn[row].string3 + "','" + sn[row].string4 + "','" + sn[row].string5 + "') from dual";
                    //err = DBAction.RunStore(err);
                    OracleCommand cmd = DBAction.con.CreateCommand();
                    OracleParameter param = new OracleParameter();
                    cmd.CommandText = query;
                    cmd.CommandType = CommandType.Text;
                    var x = cmd.ExecuteScalar();
                    err = x.ToString();

                    sn[row].string6 = err;
                    if (err != "DONE")
                    {
                        st += "<tr style='background:gray;'>";
                        st += "  <td><input id='row_" + row.ToString() + "' type='checkbox' onclick=\"ttt(this)\" disabled='disabled'/></td>";
                    }
                    else
                    {
                        st += "<tr>";
                        st += "  <td><input id='row_" + row.ToString() + "' type='checkbox' onclick=\"ttt(this)\" checked='checked'/></td>";
                    }
                    st += "  <td>" + sn[row].string2 + "</td>";
                    st += "  <td>" + sn[row].string3 + "</td>";
                    st += "  <td>" + sn[row].string4 + "</td>";
                    st += "  <td>" + sn[row].string5 + "</td>";
                    st += "  <td>" + err + "</td>";
                    st += "  </tr>";

                    if (err != "DONE")
                        txtRowToUnSave.Text += "row_" + row + "||";
                    row++;
                }

                ReturnValue = st;
                Session["importTable_model_xls"] = sn.ToArray();



            }
            catch (Exception ee)
            {
                if (ee.InnerException != null)
                    return "Error: " + ee.InnerException.Message;
                else
                    return "Error: " + ee.Message;
            }
            return ReturnValue;
        }

        protected void btnReadFile_Click(object sender, EventArgs e)
        {
            try
            {
                Entities db = new Entities();
                TB_CM_SYS_LOG lg = new TB_CM_SYS_LOG();
                string seq_key = Guid.NewGuid().ToString();
                string tit = "Import Model to Scheme by Excel";

                string st = "";
                litData.Text = "";
                var sn = (ObjectThayThe[])Session["importTable_model_xls"];
                int i = 1;
                int rowAdded = 0;
                string NewtxtRowToUnSave = "";
                foreach (var item in sn)
                {
                    if (item != null)
                    {
                        if (!txtRowToUnSave.Text.Contains(item.string1))
                        {
                            string sdf = "select pk_som_mst.FN_INCT_MD_UPL_SV('" + sn[i].string2 + "','" + sn[i].string3 + "','" + sn[i].string4 + "','" + sn[i].string5 + "','" + _currentUserEmpNo + "') from dual";
                            OracleCommand cmd = DBAction.con.CreateCommand();
                            OracleParameter param = new OracleParameter();
                            cmd.CommandText = sdf;
                            cmd.CommandType = CommandType.Text;
                            var x = cmd.ExecuteScalar();
                            string err = x.ToString();
                            if (err != "DONE")
                            { //Loi
                                st += "<tr style='background:gray;'>";
                                st += "  <td><input id='row_" + i + "' type='checkbox' onclick=\"ttt(this)\" disabled='disabled'/></td>";
                                st += "  <td>" + sn[i].string2 + "</td>";
                                st += "  <td>" + sn[i].string3 + "</td>";
                                st += "  <td>" + sn[i].string4 + "</td>";
                                st += "  <td>" + sn[i].string5 + "</td>";
                                st += "  <td>" + err + "</td>";
                                st += "  </tr>";
                                NewtxtRowToUnSave += sn[i].string1 + "||";
                            }
                            else
                            { //Khong loi
                                st += "<tr>";
                                st += "  <td><input id='row_" + i + "' type='checkbox' onclick=\"ttt(this)\" checked='checked'/></td>";
                                st += "  <td>" + sn[i].string2 + "</td>";
                                st += "  <td>" + sn[i].string3 + "</td>";
                                st += "  <td>" + sn[i].string4 + "</td>";
                                st += "  <td>" + sn[i].string5 + "</td>";
                                st += "  <td>" + err + "</td>";
                                st += "  </tr>";

                                rowAdded++;
                                //log
                                if (DBAction.LogUser == true)
                                {
                                    lg = new TB_CM_SYS_LOG();
                                    lg.ID = String.Format("{0:u}", DateTime.Now);
                                    lg.ENTRY_USER_ID = _currentUserEmpNo;
                                    lg.TITLE = tit;
                                    lg.SEQ_KEY = seq_key;
                                    lg.ACTION1 = "select pk_som_mst.FN_INCT_MD_UPL_SV('" + sn[i].string2 + "','" + sn[i].string3 + "','" + sn[i].string4 + "','" + sn[i].string5 + "','" + _currentUserEmpNo + "') from dual";
                                    lg.REMARK = err;
                                    lg.ENTRY_DATE_TIME = DateTime.Now;
                                    db.TB_CM_SYS_LOG.AddObject(lg);
                                }
                            }
                            //build lai row do'


                        }
                        else
                        {
                            if (sn[i].string6 != "DONE")
                            {
                                //bi loi khi check validate upload
                                st += "<tr style='background:gray;'>";
                                st += "  <td><input id='" + item.string1 + "' type='checkbox' onclick=\"ttt(this)\" disabled='disabled'/></td>";
                                st += "  <td>" + sn[i].string2 + "</td>";
                                st += "  <td>" + sn[i].string3 + "</td>";
                                st += "  <td>" + sn[i].string4 + "</td>";
                                st += "  <td>" + sn[i].string5 + "</td>";
                                st += "  <td>" + sn[i].string6 + "</td>";
                            }
                            else
                            {
                                ////ko bi loi, nhug ko add
                                st += "<tr>";
                                st += "  <td><input id='" + item.string1 + "' type='checkbox' onclick=\"ttt(this)\"/></td>";
                                st += "  <td>" + sn[i].string2 + "</td>";
                                st += "  <td>" + sn[i].string3 + "</td>";
                                st += "  <td>" + sn[i].string4 + "</td>";
                                st += "  <td>" + sn[i].string5 + "</td>";
                                st += "  <td>" + "Not Add yet" + "</td>";
                            }

                            st += "  </tr>";
                            NewtxtRowToUnSave += sn[i].string1 + "||";

                        }
                        i++;
                    }
                }
                txtRowToUnSave.Text = NewtxtRowToUnSave;

                litData.Text = st;
                ShowSuccess(rowAdded + " item imported.");

                //xoa het session, talbe, excel  cua form nay

                if (File.Exists(Session["filePath_model_xls"].ToString()))
                    File.Delete(Session["filePath_model_xls"].ToString());
                Session["filePath_model_xls"] = "";
                Session["importTable_model_xls"] = "";
            }
            catch (Exception ee)
            {
                ShowError(ee);
            }


        }
        protected void btnTemplate_Click(object sender, EventArgs e)
        {
            Response.Redirect("Model_upload_template.xls");
        }


        public void ShowError(string er)
        {
            ScriptManager.RegisterClientScriptBlock(this, this.GetType(), "abcdefgh", "<div class=\"notification error\"><div class=\"text\"><p><strong>" + er + " </strong> </p></div></div>", false);
        }
        public void ShowError(Exception ex)
        {
            if (ex.InnerException != null)
            {
                string er = ex.InnerException.Message + " at " + ex.TargetSite.Name + "()";
                ScriptManager.RegisterClientScriptBlock(this, this.GetType(), "abcdefgh", "<div class=\"notification error\"><div class=\"text\"><p><strong> " + er + " </strong></p></div></div>", false);
            }
        }
        public void ShowInfo(string er)
        {
            ScriptManager.RegisterClientScriptBlock(this, this.GetType(), "abcdefgh", "<div class=\"notification info\"><div class=\"text\"><p><strong> " + er + " </strong></p></div></div>", false);
        }
        public void ShowInfo(Exception ex)
        {
            if (ex.InnerException != null)
            {
                string er = ex.InnerException.Message + " at " + ex.TargetSite.Name + "()";
                ScriptManager.RegisterClientScriptBlock(this, this.GetType(), "abcdefgh", "<div class=\"notification info\"><div class=\"text\"><p><strong> " + er + " </strong></p></div></div>", false);
            }
        }
        public void ShowWarning(string er)
        {
            ScriptManager.RegisterClientScriptBlock(this, this.GetType(), "abcdefgh", "<div class=\"notification warning\"><div class=\"text\"><p><strong> " + er + " </strong></p></div></div>", false);
        }
        public void ShowWarning(Exception ex)
        {
            if (ex.InnerException != null)
            {
                string er = ex.InnerException.Message + " at " + ex.TargetSite.Name + "()";
                ScriptManager.RegisterClientScriptBlock(this, this.GetType(), "abcdefgh", "<div class=\"notification warning\"><div class=\"text\"><p><strong> " + er + " </strong></p></div></div>", false);
            }
        }
        public void ShowSuccess(string er)
        {
            ScriptManager.RegisterClientScriptBlock(this, this.GetType(), "abcdefgh", "<div class=\"notification success\"><div class=\"text\"><p><strong> " + er + " </strong></p></div></div>", false);
        }
        public void ShowSuccess(Exception ex)
        {
            if (ex.InnerException != null)
            {
                string er = ex.InnerException.Message + " at " + ex.TargetSite.Name + "()";
                ScriptManager.RegisterClientScriptBlock(this, this.GetType(), "abcdefgh", "<div class=\"notification success\"><div class=\"text\"><p><strong> " + er + " </strong></p></div></div>", false);
            }
        }
        public void ShowTip(string er)
        {
            ScriptManager.RegisterClientScriptBlock(this, this.GetType(), "abcdefgh", "<div class=\"notification tip\"><div class=\"text\"><p><strong> " + er + "</strong> </p></div></div>", false);
        }
        public void ShowTip(Exception ex)
        {
            if (ex.InnerException != null)
            {
                string er = ex.InnerException.Message + " at " + ex.TargetSite.Name + "()";
                ScriptManager.RegisterClientScriptBlock(this, this.GetType(), "abcdefgh", "<div class=\"notification tip\"><div class=\"text\"><p><strong> " + er + " </strong></p></div></div>", false);
            }
        }

        protected void btnShowUploadResult_Click(object sender, EventArgs e)
        {
            litData.Text = readExcel(false);
        }
    }
}